package com.hsnn.medstgmini.pro.base.dao.Impl;

import java.math.BigInteger;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.annotation.Resource;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.Transformers;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.Constants;
import com.hsnn.medstgmini.base.dict.dao.impl.BaseHibernateDAO;
import com.hsnn.medstgmini.base.dict.service.DicAreaManager;
import com.hsnn.medstgmini.base.std.model.StdCompinfo;
import com.hsnn.medstgmini.base.std.service.StdCompinfoManager;
import com.hsnn.medstgmini.base.std.service.StdHospInfoManager;
import com.hsnn.medstgmini.dao.HibernateSqlUtil;
import com.hsnn.medstgmini.pro.base.dao.BaseDelrelationDao;
import com.hsnn.medstgmini.pro.base.model.BaseDelrelation;
import com.hsnn.medstgmini.pro.base.model.BaseDelrelationLog;
import com.hsnn.medstgmini.pro.base.model.form.BaseDelrelationForm;
import com.hsnn.medstgmini.pro.base.service.BaseGoodsManager;
import com.hsnn.medstgmini.util.AreaUtil;
import com.hsnn.medstgmini.util.DateUtil;
import com.hsnn.medstgmini.util.Pagination;
import com.hsnn.medstgmini.util.PinYin;

@Repository("baseDelrelationDao")
public class BaseDelrelationDaoImpl extends BaseHibernateDAO implements BaseDelrelationDao {

	@Autowired
	private HibernateSqlUtil dao;
	
	@Autowired
	private HibernateSqlUtil utilDao;
	@Autowired
	private StdHospInfoManager hospManager;
	@Autowired
	private BaseGoodsManager baseGoodsManager;
	@Autowired
	private DicAreaManager dicAreaManager;
	@Autowired
	private StdCompinfoManager compManager;
	

	public void confirmDel(Pagination page) {
		BaseDelrelation data = (BaseDelrelation) dao.getUniquerResult(
				BaseDelrelation.class, page.getConditions().get("delRelId"));
		try {
			data.setStatus(Short.valueOf(page.getConditions().get("status")));
			dao.add(data);
			writeLog(data);
		} catch (Exception e) {
			// TODO: handle exception
		}

	}

	/**
	 * 
	 * @category 写配送关系日志
	 * @author 邱磊
	 * @date 2015年6月17日 下午9:05:25
	 * @param data
	 */
	private void writeLog(BaseDelrelation data) {
		if (null != data) {
			BaseDelrelationLog log = new BaseDelrelationLog();
			BeanUtils.copyProperties(data, log);
			log.setAreaCode(data.getDicArea().getAreaid());
			log.setProdCompId(data.getStdCompinfo()==null?"":data.getStdCompinfo().getCompId());
			log.setDelCompId(data.getStdPSCompinfo()==null?"":data.getStdPSCompinfo().getCompId());
			log.setLogId(UUID.randomUUID().toString());
			dao.add(log);
		}
	}

	@Resource(name = "sessionFactory")
	private SessionFactory sessionFactory;

	/**
	 * @category 根据id查询配送企业详情
	 * @author 张俊
	 * @date 2015-06-18
	 * @param id
	 * @return
	 */
	public BaseDelrelation getBaseDelrelationById(String id) {
		if (id != null && !"".equals(id)) {
			Session session = sessionFactory.getCurrentSession();
			Criteria criteria = session.createCriteria(BaseDelrelation.class);
			criteria.add(Restrictions.eq("delRelId", id));
			return (BaseDelrelation) criteria.uniqueResult();
		}
		return null;
	}

	/**
	 * @category 解除配送关系
	 * @author 张俊
	 * @date 2015-06-18
	 * @param baseDelrelation
	 * @return
	 */
	public void changeStatus(BaseDelrelation baseDelrelation) {
		if (baseDelrelation != null && baseDelrelation.getDelRelId() != null) {
			Session session = sessionFactory.getCurrentSession();
			StringBuffer sb = new StringBuffer();
			sb.append("update BaseDelrelation set ");
			sb.append(" status = 2 ");
			sb.append(", updDatetime = :updDatetime ");
			sb.append(" where delRelId = :delRelId ");

			Query query = session.createQuery(sb.toString());
			query.setTimestamp("updDatetime", new Date());
			query.setString("delRelId", baseDelrelation.getDelRelId());
			query.executeUpdate();
			writeDelLog(baseDelrelation);
			session.flush();
		}
	}

	/**
	 * 
	 * @category 查询当前配送企业的所有药品配送关系
	 * @author 单大伟
	 * @date 2015年6月18日
	 * @param page
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Pagination getBaseDelRelationList(Pagination page) {
		StringBuffer sql = new StringBuffer()
				.append("SELECT a.del_rel_id,a.goods_code,b.drug_code,b.qualityspec_name drug_name,b.form_name,")
				.append("b.spec_name,b.factor,b.unit_name unit,b.material_name,b.prod_compname comp_name,a.area_name,b.pro_price_limit,b.drug_code drug_id,b.prod_compcode prod_comp_id, ")
				.append("a.status,b.retail_price_limit  ");
		StringBuffer sqlCount = new StringBuffer().append("select count(*) ");
		
		StringBuffer sqlEnd = new StringBuffer().append(" from base_delrelationship a left join base_goods_item b on a.goods_code=b.goods_code")
				.append(" where a.status=:status and b.status=1  and b.proj_status=1")
				.append(" and DATE_FORMAT(b.proj_starttime,'%Y-%m-%d') <= DATE_FORMAT(SYSDATE(),'%Y-%m-%d') and DATE_FORMAT(b.proj_endtime,'%Y-%m-%d') >= DATE_FORMAT(SYSDATE(),'%Y-%m-%d')")
				.append(" and a.del_comp_code=:compCode");
		
		sql.append(sqlEnd);
		sqlCount.append(sqlEnd);
		
		Map<String, String> map = page.getConditions();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("status", map.get("status").trim());
		params.put("compCode", map.get("compCode").trim());
		if (StringUtils.isNotBlank(map.get("goodsCode"))) {
			sql.append(" and a.goods_code =:goodsCode");
			sqlCount.append(" and a.goods_code =:goodsCode");
			params.put("goodsCode", map.get("goodsCode").trim());
		}
		if (StringUtils.isNotBlank(map.get("drugCode"))) {
			sql.append(" and b.drug_code =:drugCode");
			sqlCount.append(" and b.drug_code =:drugCode");
			params.put("drugCode", map.get("drugCode").trim());
		}
		if (StringUtils.isNotBlank(map.get("drugName"))) {
			if (PinYin.isPinYin(map.get("drugName").trim())) {
				sql.append(" and b.qualityspec_name_spel like :drugName");
				sqlCount.append(" and b.qualityspec_name_spel like :drugName");
				params.put("drugName", "%" + map.get("drugName").trim().toUpperCase() + "%");
			} else {
				sql.append(" and b.qualityspec_name like :drugName");
				sqlCount.append(" and b.qualityspec_name like :drugName");
				params.put("drugName", "%" + map.get("drugName").trim() + "%");
			}

		}
		if (StringUtils.isNotBlank(map.get("compName"))) {
			sql.append(" and (b.prod_compname like :compName or b.prod_compname_spel like :compSpelCode)");
			sqlCount.append(" and (b.prod_compname like :compName or b.prod_compname_spel like :compSpelCode)");
			params.put("compName", "%" + map.get("compName").trim() + "%");
			params.put("compSpelCode",  "%" + map.get("compName").trim().toUpperCase() + "%");
		}
		if (StringUtils.isNotBlank(map.get("areaCode"))) {
			sql.append(" and a.area_code=:areaCode");
			sqlCount.append(" and a.area_code=:areaCode");
			params.put("areaCode", map.get("areaCode").trim());
		}
		if (StringUtils.isNotBlank(map.get("fatherCode"))) {
			sql.append(" and a.area_code like :fatherCode");
			sqlCount.append(" and a.area_code like :fatherCode");
			params.put("fatherCode", AreaUtil.getAreaPrefix(map.get("fatherCode").trim())+"%");
		}
		Query query = sessionFactory.getCurrentSession().createSQLQuery(sql.toString());
		if (params != null) {
			Iterator<?> list = params.entrySet().iterator();
			while (list.hasNext()) {
				Map.Entry<String, ?> entry = (Map.Entry<String, ?>) list.next();
				query.setParameter((String) entry.getKey(), entry.getValue());
			}
		}
		if (page.getMaxResults() > 0) {			
			Query countQuery = sessionFactory.getCurrentSession().createSQLQuery(sqlCount.toString());
			if (params != null) {
				Iterator<?> list = params.entrySet().iterator();
				while (list.hasNext()) {
					Map.Entry<String, ?> entry = (Map.Entry<String, ?>) list.next();
					countQuery.setParameter((String) entry.getKey(), entry.getValue());
				}
			}
			BigInteger records = (BigInteger) countQuery.uniqueResult();
			page.setRecords(records.longValue());
		}
		query.setFirstResult(page.getFirstResult());
		query.setMaxResults(page.getMaxResults());

		List<?> list = query.list();
		page.setRows(list);
		
		return page;
	}

	/**
	 * 
	 * @category 配送关系日志列表
	 * @author 张俊
	 * @date 2015年6月23日
	 * @param page
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<BaseDelrelationLog> getBaseDelRelationLogList(Pagination page,
			BaseDelrelationLog baseDelrelationLog) {
		Session session = sessionFactory.getCurrentSession();
		StringBuffer sql = new StringBuffer();
		sql.append("select * from base_delrelationship_log l where l.del_rel_id = :delRelId ");
		if (baseDelrelationLog != null) {
			Date start = baseDelrelationLog.getStartTime();
			if (start != null) {
				sql.append(" and l.upd_datetime >= :startTime ");
			}
			Date end = baseDelrelationLog.getEndTime();
			if (end != null) {
				sql.append(" and l.upd_datetime <= :endTime ");
			}
		}

		StringBuffer countSql = new StringBuffer();
		countSql.append(" select count(*) from ( ");
		countSql.append(sql.toString());
		countSql.append(" ) s where 1 = 1 ");

		SQLQuery query = session.createSQLQuery(sql.toString());
		Query countQuery = session.createSQLQuery(countSql.toString());

		query.setString("delRelId", baseDelrelationLog.getDelRelId());
		countQuery.setString("delRelId", baseDelrelationLog.getDelRelId());
		if (baseDelrelationLog != null) {
			Date start = baseDelrelationLog.getStartTime();
			if (start != null) {
				query.setTimestamp("startTime", start);
				countQuery.setTimestamp("startTime", start);
			}
			Date end = baseDelrelationLog.getEndTime();
			if (end != null) {
				query.setTimestamp("endTime", end);
				countQuery.setTimestamp("endTime", end);
			}
		}
		BigInteger total = (BigInteger) countQuery.uniqueResult();
		page.setRecords(total.longValue());

		query.setFirstResult(page.getFirstResult());
		query.setMaxResults(page.getMaxResults());
		query.addEntity(BaseDelrelationLog.class);
		return query.list();
	}
	public Pagination getBaseDelRelationLogByPs(Pagination page){
		StringBuffer sql = new StringBuffer()
		.append("SELECT a.goods_code,b.drug_code,b.qualityspec_name drug_name,b.form_name,")
		.append("b.spec_name,b.factor,b.unit_name unit,b.material_name,a.prod_comp_name,a.area_name,b.pro_price_limit,a.status,a.upd_datetime,f.name,b.drug_code drug_id,a.prod_comp_code")
		.append(" from base_delrelationship_log a left join base_goods_item b on a.goods_code=b.goods_code")
		.append(" left join sys_user f on a.upd_user=f.id")
		.append(" where  b.status=1 and b.proj_status=1")
		.append(" and DATE_FORMAT(b.proj_starttime,'%Y-%m-%d') <= DATE_FORMAT(SYSDATE(),'%Y-%m-%d') and DATE_FORMAT(b.proj_endtime,'%Y-%m-%d') >= DATE_FORMAT(SYSDATE(),'%Y-%m-%d')")
		.append(" and a.del_comp_code=:delCompId");
		Map<String, Object> params = new HashMap<String, Object>();
		Map<String, String> map = page.getConditions();
		params.put("delCompId", map.get("delCompId"));
		if (StringUtils.isNotBlank(map.get("goodsCode"))){
			sql.append(" and a.goods_code=:goodsCode");
			params.put("goodsCode", map.get("goodsCode"));
		}
		if (StringUtils.isNotBlank(map.get("drugCode"))){
			sql.append(" and b.drug_code=:drugCode");
			params.put("drugCode", map.get("drugCode"));
		}
		if (StringUtils.isNotBlank(map.get("drugName"))){
			if (PinYin.isPinYin(map.get("drugName"))){
				sql.append(" and b.qualityspec_name_spel like :drugName");
				params.put("drugName", "%"+map.get("drugName").trim().toUpperCase()+"%");
			} else {
				sql.append(" and b.qualityspec_name like :drugName");
				params.put("drugName", "%"+map.get("drugName").trim()+"%");
			}
		}
		if (StringUtils.isNotBlank(map.get("compName"))){
			sql.append(" and a.comp_name like :compName");
			params.put("compName", "%"+map.get("compName").trim()+"%");
		}
		if (StringUtils.isNotBlank(map.get("areaCode"))){
			sql.append(" and a.area_code=:areaCode");
			params.put("areaCode", map.get("areaCode"));
		}
		if (StringUtils.isNotBlank(map.get("fatherCode"))){
			sql.append(" and a.area_code like :fatherCode");
			params.put("fatherCode", AreaUtil.getAreaPrefix(map.get("fatherCode"))+"%");
		}
		if (StringUtils.isNotBlank(map.get("startTime"))){
			sql.append(" and a.upd_datetime>=:startTime");
			params.put("startTime", map.get("startTime"));
		}
		if (StringUtils.isNotBlank(map.get("endTime"))){
			sql.append(" and a.upd_datetime<=:endTime");
			params.put("endTime", map.get("endTime"));
		}
		sql.append(" order by a.goods_code desc,a.upd_datetime desc");
		return dao.getSqlPageList(sql.toString(), params, page);
	}
	/**
	 * @category 配送企业解除配送关系日志
	 * @author 张俊
	 * @date 2015-06-18
	 * @param baseDelrelation
	 * @return
	 */
	private void writeDelLog(BaseDelrelation baseDelrelation) {

		Session session = sessionFactory.getCurrentSession();
		BaseDelrelation bd = getBaseDelrelationById(baseDelrelation
				.getDelRelId());
		if (bd != null) {
			BaseDelrelationLog bdl = new BaseDelrelationLog();
			bdl.setLogId(UUID.randomUUID().toString());
			bdl.setDelRelId(bd.getDelRelId());
			// bdl.setDrugId(bd.getDrugId());
			bdl.setGoodsCode(bd.getGoodsCode());
			bdl.setProdCompId(bd.getProdCompId());
			bdl.setAreaCode(bd.getAreaCode());
			bdl.setDelCompId(bd.getDelCompId());
			bdl.setHospId(bd.getHospId());
			bdl.setStatus(bd.getStatus());
			bdl.setUpdDatetime(bd.getUpdDatetime());
			bdl.setUpdUser(bd.getUpdUser());
			session.save(bdl);
		}
	}

	public Pagination getDrugByPS(Pagination page) {
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT a.goods_code,b.drug_code,b.qualityspec_name drug_name,b.form_name,");
		sql.append("b.spec_name,b.factor,b.unit_name unit,b.material_name,b.prod_compname comp_name,count(distinct(a.area_code)),b.pro_price_limit,b.drug_code drug_id,b.prod_compcode prod_comp_id");
		sql.append(" from base_delrelationship a left join base_goods_item b on a.goods_code=b.goods_code");
		sql.append(" where a.status=1 and b.status=1  and b.proj_status=1");
		sql.append(" and b.proj_starttime <= SYSDATE() and b.proj_endtime>=SYSDATE()");
		sql.append("and a.del_comp_code=:delCompId");
		Map<String, Object> params = new HashMap<String, Object>();
		Map<String, String> map = page.getConditions();
		params.put("delCompId", map.get("compId"));
		if (StringUtils.isNotBlank(map.get("goodsCode"))) {
			sql.append(" and a.goods_code = :goodsCode ");
			params.put("goodsCode", map.get("goodsCode"));
		}
		if (StringUtils.isNotBlank(map.get("drugCode"))) {
			sql.append(" and b.drug_code = :drugCode ");
			params.put("drugCode", map.get("drugCode"));
		}
		if (StringUtils.isNotBlank(map.get("drugName"))) {
			if (PinYin.isPinYin(map.get("drugName").trim())) {
				sql.append(" and b.qualityspec_name_spel like :drugName");
				params.put("drugName", "%"
						+ map.get("drugName").trim().toUpperCase() + "%");
			} else {
				sql.append(" and b.qualityspec_name like :drugName");
				params.put("drugName", "%" + map.get("drugName").trim() + "%");
			}

		}
		if (StringUtils.isNotBlank(map.get("compName"))) {
			sql.append(" and (b.prod_compname like :compName or b.prod_compname_spel like :compName)");
			params.put("compName", "%" + map.get("compName").trim() + "%");
		}
		sql.append(" group by a.goods_code");
		return dao.getSqlPageList(sql.toString(), params, page);
	}

	/**
	 * 
	 * @category 配送企业配送的药品的所有配送区域
	 * @author 张俊
	 * @date 2015-06-25 17:12:44
	 * @param
	 * @return
	 */
	public Pagination getDrugByPSArea(Pagination page) {
		StringBuffer sql = new StringBuffer()
				.append("SELECT a.del_rel_id,d.area_id,d.area_name,d.father_id,f.area_name fathername")
				.append(" from base_delrelationship a left join base_goods_item b on a.goods_code=b.goods_code")
				.append(" left join dic_area d on d.area_id=a.area_code")
				.append(" left join dic_area f on f.area_id=d.father_id")
				.append(" where a.status=1 and b.status=1  and b.proj_status=1")
				.append(" and b.proj_starttime <= SYSDATE() and b.proj_endtime>=SYSDATE()")
				.append(" and a.del_comp_code = :delCompId ")
				.append(" and a.goods_code=:goodsCode");
		Map<String, Object> params = new HashMap<String, Object>();
		Map<String, String> map = page.getConditions();
		params.put("delCompId", map.get("compId"));
		params.put("goodsCode", map.get("goodsCode"));
		if (StringUtils.isNotBlank(map.get("areaCode"))) {
			sql.append(" and a.area_code = :areaCode ");
			params.put("areaCode", map.get("areaCode"));
		}
		if (StringUtils.isNotBlank(map.get("fatherCode"))) {
			sql.append(" and d.father_id = :fatherCode ");
			params.put("fatherCode", map.get("fatherCode"));
		}
		return dao.getSqlPageList(sql.toString(), params, page);
	}
	public Pagination getDelDrugDataList(Pagination page) {
		StringBuffer sql = new StringBuffer()
				.append("select a.goods_code,a.drug_code,a.qualityspec_name,a.form_name,")
				.append("a.spec_name,a.factor,a.unit_name,a.prod_compname,a.proj_name,")
				.append(" a.proj_id,a.material_name,a.pro_price_limit,a.prod_compcode,a.area_id area_code ")
				.append(" from base_goods_item a ")
				.append(" WHERE a.status=1  and a.proj_starttime<= SYSDATE() and a.proj_endtime>=SYSDATE()");
		Map<String, String> map = page.getConditions();
		Map<String, Object> params = new HashMap<String, Object>();
		if (null != map && map.size() > 0) {
			if (StringUtils.isNotBlank(map.get("compName"))) {
				sql.append(" and (a.prod_compname like :compName or a.prod_compname_spel like :compName)");
				params.put("compName", "%"+ map.get("compName") + "%");
			}
			if (StringUtils.isNotBlank(map.get("drugCode"))) {
				sql.append(" and a.drug_code = :drugCode");
				params.put("drugCode", map.get("drugCode"));
			}
			if (StringUtils.isNotBlank(map.get("drugName"))) {
				sql.append(" and (a.qualityspec_name like :drugName or a.qualityspec_name_spel like :drugName)");
				params.put("drugName", "%" + map.get("drugName") + "%");
			}
			if (StringUtils.isNotBlank(map.get("projName"))) {
				sql.append(" and a.proj_name like :projName");
				params.put("projName", "%" + map.get("projName") + "%");
			}
			if (StringUtils.isNotBlank(map.get("projId"))) {
				sql.append(" and a.proj_id = :projId");
				params.put("projId", map.get("projId"));
			}
			if (StringUtils.isNotBlank(map.get("goodsCode"))) {
				sql.append(" and a.goods_code = :goodsCode");
				params.put("goodsCode", map.get("goodsCode"));
			}
		}
		page = dao.getSqlPageMapList(sql.toString(), params, page);
		return page;
	}
	@Value("#{sysProp['system.province']}")
    private String province;//省份
	public Pagination getCenterDelByAreaList(Pagination page){
		StringBuffer sql = new StringBuffer()
		.append("select a.area_id,a.area_name,a.father_id,c.area_name fathername,")
		.append(" (select count(b.del_comp_id) from base_delrelationship b where b.status !=1 and b.goods_code=:goodsCode and b.area_code=a.area_id)")
		.append(" from dic_area a left join dic_area c on c.area_id=a.father_id")
		.append("  where a.status=1 and  a.area_id like :province and a.area_id not like '%00'");
		Map<String, Object> params = new HashMap<String, Object>();
		Map<String, String> map = page.getConditions();
		params.put("goodsCode", map.get("goodsCode"));
		params.put("province", province.substring(0, 2)+"%");
		if (StringUtils.isNotBlank(map.get("areaCode"))) {
			sql.append(" and a.area_id=:areaId");
			params.put("areaId", map.get("areaCode"));
		}
		if (StringUtils.isNotBlank(map.get("fatherCode"))) {
			sql.append(" and a.father_id = :fatherCode");
			params.put("fatherCode", map.get("fatherCode"));
		}
		sql.append(" order by a.area_id");
		page = dao.getSqlPageList(sql.toString(), params, page);
		return page;
	}
	
	
	
	public void removeDell(List<String> list,String updUser){
		Session session = super.getSession();
		StringBuffer sql = new StringBuffer()
		.append("update base_delrelationship set status = :status,upd_user = :updUser,upd_datetime = :updDatetime  where del_rel_id in (:list) ");
		Query query = session.createSQLQuery(sql.toString());
		query.setParameter("status", Constants.DEL_STATUS_CONFIRMED);
		query.setParameter("updUser", updUser);
		query.setParameter("updDatetime", DateUtil.dateTime(new Timestamp(System.currentTimeMillis())));
		query.setParameterList("list", list);
		query.executeUpdate();
		session.flush();
		for(String delRelId : list){
			writeLogBySql(delRelId);
		}
	}
	
	private void writeLogBySql(String delRelId){
		Session session = super.getSession();
		StringBuffer sql = new StringBuffer()
		.append("insert into base_delrelationship_log select :uuid,t.* from base_delrelationship t where t.del_rel_id = :delRelId ");
		Query query = session.createSQLQuery(sql.toString());
		query.setString("uuid", UUID.randomUUID().toString());
		query.setString("delRelId", delRelId);
		query.executeUpdate();
		session.flush();
	} 
	/**
	 * 
	 *@category 获取未被遴选的配送企业
	 *@author 邱磊
	 *@date 2015年11月19日 下午8:28:38
	 *@param 
	 *@return Pagination
	 */
	public Pagination getSelectDelData(Pagination page){
		StringBuffer sql = new StringBuffer()
		.append("select * from base_del_area_rel t")
		.append(" where 1 = 1");
		Map<String, String> map = page.getConditions();
		Map<String, Object> params = new HashMap<String, Object>();
		if (StringUtils.isNotBlank(map.get("status"))){
			sql.append(" and t.status= :status");
			params.put("status", map.get("status"));
		}
		if (StringUtils.isNotBlank(map.get("areaCode"))){
			sql.append(" and t.area_code= :areaCode");
			params.put("areaCode", map.get("areaCode"));
		}
		if (StringUtils.isNotBlank(map.get("compCode"))){
			sql.append(" and t.del_comp_code= :compCode");
			params.put("compCode", map.get("compCode"));
		}
		if (StringUtils.isNotBlank(map.get("compName"))){
			sql.append(" and (t.del_comp_name like :compName or t.del_comp_name_spel like :compNameSpel)");
			params.put("compName", "%" + map.get("compName").trim() + "%");
			params.put("compNameSpel", "%" + map.get("compName").trim().toUpperCase() + "%");
		}
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}
	/**
	 * 
	 *@category 遴选配送企业
	 *@author 邱磊
	 *@date 2015年11月26日 上午9:41:18
	 *@param
	 */
	public void confirmDelArea(List<BaseDelrelationForm> list){
		for (int i = 0; i < list.size(); i++){
			BaseDelrelationForm form = list.get(i);
			StringBuffer sql = new StringBuffer()
			.append("update base_del_area_rel set status= :status,del_range= :delRange,sel_datetime=SYSDATE(),sel_user=:userId where base_del_area_rel_id= :delreId");
			Map<String, Object> params = new HashMap<String, Object>();
			params.put("status", form.getStatus());
			params.put("delRange", form.getRange());
			params.put("delreId", form.getDelRelId());
			params.put("userId", form.getUpdUser());
			dao.executeSql(sql.toString(), params);
		}
	}
	/**
	 * 
	 *@category 
	 *@author 邱磊
	 *@date 2015年11月26日 上午9:41:52
	 *@param
	 */
	@SuppressWarnings("unchecked")
	public Map<String, Object> getHaveDelNum(String areaCode, int range, List<String> paramList){
		StringBuffer sql = new StringBuffer()
		.append("select count(*) as delCount from base_del_area_rel t")
		.append(" where t.area_code= :areaCode")
		.append(" and (t.del_range = :rangeAll or t.del_range = :range)")
		.append(" and t.status = :status ");
		Map<String, Object> params = new HashMap<String, Object>();
		if (null != paramList && paramList.size() > 0){
			sql.append(" and t.base_del_area_rel_id not in (:paramList)");
			params.put("paramList", paramList);
		}
		params.put("areaCode", areaCode);
		params.put("rangeAll", Constants.DEL_RANGE_ALL);
		params.put("range", range);
		params.put("status", Constants.DEL_SELECT_STATUS_LINXUANTONGUO);
		
		List<Map<String, Object>> list = (List<Map<String, Object>>) dao.getSqlMapList(sql.toString(), params);
		if (null != list && list.size() > 0){
			return list.get(0);
		}
		return null;
	}

	@Override
	public Pagination batchDeclare(Pagination page) {
		StdCompinfo sc = (StdCompinfo) page.getForm();
		String param = page.getConditions().get("param");
		String userId = page.getConditions().get("userId");
		String[] areaIdes= param.split(",");
		List<String> list =new ArrayList<String>();
		for(int i=0,num=areaIdes.length;i<num;i++){
			list.add(areaIdes[i]);
		}
		StringBuffer sb =new StringBuffer()
		.append(" insert into base_del_area_rel ")
		.append("(base_del_area_rel_id,area_code,area_name,ar_shortname,ar_fullname,del_comp_code,del_comp_name,del_comp_name_spel,del_comp_status,status,upd_user,upd_datetime  )")
		.append(" select uuid(),a.area_id,a.area_name,a.ar_shortname,a.ar_fullname,:compCode,:compName,:compNameSpel,:compStatus,:status,:updUser,:updDatetime FROM dic_area a where a.area_id in(:list)  ");
		
		Session session = getSession();
		SQLQuery query = session.createSQLQuery(sb.toString());
		query.setParameter("compCode", sc.getCompCode())
		.setParameter("compName", sc.getCompName())
		.setParameter("compNameSpel", sc.getCompSpelCode())
		.setParameter("compStatus", sc.getStatus())
		.setParameter("status", Constants.DEL_SELECT_STATUS_WEILINXUAN)
		.setParameter("updUser", userId)
		.setParameter("updDatetime", new Date())
		.setParameterList("list", list);
		int col = query.executeUpdate();
		session.flush();
		session.clear();
		page.setTotal(col);
		return page;
	}

	/**
	 *@category 获取遴选结果列表(配送企业)
	 *@author 应晓川
	 *@date 2015年11月20日17:00:02
	 *@param 
	 *@return Pagination
	 */
	@Override
	public Pagination getDelRangeResultByPs(Pagination page) {
		StringBuffer sb = new StringBuffer()
		.append(" select area_code areaCode,area_name areaName,ar_fullname arFullname,status,del_range delRange ")
		.append(" from base_del_area_rel where del_comp_code=:compCode ") ;
		Map<String, String> map = page.getConditions();
		if (StringUtils.isNotBlank(map.get("areaName"))){
			sb.append(" and area_name like :areaName");
			map.put("areaName", "%"+map.get("areaName")+"%");		
		}
		sb.append(" order by upd_datetime desc,area_code  ");
		page.setRows(dao.getSqlMapList(sb.toString(), map));  
		return page;
	}
	public Pagination getDelByHospData(Pagination page){
		StringBuffer sql = new StringBuffer()
		.append("SELECT distinct a.hosp_code,b.hosp_name,c.dic_name as hospClassify,d.area_name,e.dic_name as hospLevel from bid_projhospital a")
		.append(" left join std_hospinfo b on a.hosp_code=b.hosp_code")
		.append(" left join dic_sysdic c on b.hosp_classify=c.dic_code and c.classify_code= :hospClass")
		.append(" left join dic_area d on b.area_id=d.area_id")
		.append(" left join dic_sysdic e on e.dic_code=b.hosp_level_id and e.classify_code= :hospLevelClass")
		.append(" where a.status=1 and DATE_FORMAT(a.proj_starttime,'%Y-%m-%d') <= DATE_FORMAT(SYSDATE(),'%Y-%m-%d')")
		.append(" and DATE_FORMAT(a.proj_endtime,'%Y-%m-%d') >= DATE_FORMAT(SYSDATE(),'%Y-%m-%d')");
		Map<String, Object> params = new HashMap<String, Object>();
		Map<String, String> map = page.getConditions();
		params.put("hospClass", Constants.HOSP_CAT);
		params.put("hospLevelClass", Constants.HOSP_LEVEL);
		if (StringUtils.isNotBlank(map.get("hospCode"))){
			sql.append(" and a.hosp_code = :hospCode");
			params.put("hospCode", map.get("hospCode"));
		}
		if (StringUtils.isNotBlank(map.get("hospName"))){
			sql.append(" and (b.hosp_name like :hospName or b.hosp_spel_code like :hospNameSpel)");
			params.put("hospName", "%" + map.get("hospName").trim() + "%");
			params.put("hospNameSpel", "%" + map.get("hospName").trim().toUpperCase()+ "%");
		}
		if (StringUtils.isNotBlank(map.get("hospClassify"))){
			sql.append(" and c.dic_code = :hospClassify");
			params.put("hospClassify", map.get("hospClassify"));
		}
		if (StringUtils.isNotBlank(map.get("hospLevel"))){
			sql.append(" and e.dic_code = :hospLevel");
			params.put("hospLevel", map.get("hospLevel"));
		}
		if (StringUtils.isNotBlank(map.get("areaId"))){
			sql.append(" and b.area_id = :areaCode");
			params.put("areaCode", map.get("areaId"));
		}
		if (StringUtils.isNotBlank(map.get("fatherId"))){
			sql.append(" and d.father_id = :fatherId");
			params.put("fatherId", map.get("fatherId"));
		}
		sql.append(" and a.hosp_code not in")
		.append(" (select t.hosp_code from base_delrelationship t where t.hosp_code is not null and t.prod_comp_code = :prodCompCode)");
		params.put("prodCompCode", map.get("compCode"));
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}
	
	
	public Pagination getSetDelByHospWithDelcompData(Pagination page){
		StringBuffer sql = new StringBuffer()
		.append("select distinct t.comp_code,a.comp_name,a.org_code,b.contact_name,b.contact_telephone,")
		.append(" (select count(*) from base_delrelationship m where m.del_comp_code=t.comp_code and m.hosp_code is not null and m.prod_comp_code = :compCode) as hospCount")
		.append(" from bid_projcompany t ")
		.append(" left join std_compinfo a on a.comp_code=t.comp_code")
		.append(" left join std_compinfo_detail b on b.comp_id=a.comp_id")
		.append(" where t.status = 1");
		Map<String, Object> params = new HashMap<String, Object>();
		Map<String, String> map = page.getConditions();
		params.put("compCode", map.get("compCode"));
		if (StringUtils.isNotBlank(map.get("delCompCode"))){
			sql.append(" and t.comp_code = :delCompCode");
			params.put("delCompCode", map.get("delCompCode"));
		}
		if (StringUtils.isNotBlank(map.get("delCompName"))){
			sql.append(" and (a.comp_name like :delCompName or a.comp_spel_code like :delCompNameSpel)");
			params.put("delCompName", "%" + map.get("delCompName").trim() + "%");
			params.put("delCompNameSpel", "%" + map.get("delCompName").trim().toUpperCase() + "%");
		}
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}
	
	
	public Pagination getSetDelByHospWithLXDelcompData(Pagination page){
		StringBuffer sql = new StringBuffer()
		.append("select distinct t.del_comp_code as comp_code,t.del_comp_name as comp_name,a.org_code,b.contact_name,b.contact_telephone,")
		.append(" (select count(*) from base_delrelationship m where m.del_comp_code=t.del_comp_code and m.hosp_code is not null and m.prod_comp_code = :compCode) as hospCount")
		.append("  from base_del_area_rel t ")
		.append(" left join std_compinfo a on a.comp_code=t.del_comp_code")
		.append(" left join std_compinfo_detail b on b.comp_id=a.comp_id")
		.append(" where t.status = 1");
		Map<String, Object> params = new HashMap<String, Object>();
		Map<String, String> map = page.getConditions();
		params.put("compCode", map.get("compCode"));
		if (StringUtils.isNotBlank(map.get("delCompCode"))){
			sql.append(" and t.del_comp_code = :delCompCode");
			params.put("delCompCode", map.get("delCompCode"));
		}
		if (StringUtils.isNotBlank(map.get("delCompName"))){
			sql.append(" and (a.comp_name like :delCompName or a.comp_spel_code like :delCompNameSpel)");
			params.put("delCompName", "%" + map.get("delCompName").trim() + "%");
			params.put("delCompNameSpel", "%" + map.get("delCompName").trim().toUpperCase() + "%");
		}
		if (StringUtils.isNotBlank(map.get("hospClass"))){
			sql.append(" and (t.del_range = 0 or t.del_range = :delRange)");
			params.put("delRange", map.get("hospClass"));
		}
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}
	
	
	
	@SuppressWarnings("unchecked")
	public void setDelByHospWithDelcomp(Pagination page){
		List<BaseDelrelation> list = (List<BaseDelrelation>) page.getRows();
		Map<String, String> map = page.getConditions();
		Map<String, Object> hosp = hospManager.getStdHospInfoByCode(map.get("hospCode"));
		Map<String, Object> area = dicAreaManager.getAreaByAreaId(hosp.get("area_id").toString());
		Map<String, Object> comp = compManager.getCompinfoByCode(map.get("compCode"));
		for (int i = 0; i < list.size(); i++) {
			BaseDelrelation data = list.get(i);
			String uuid = UUID.randomUUID().toString();
			StringBuffer sql = new StringBuffer()
			.append("insert into base_delrelationship (del_rel_id,goods_code,prod_comp_code,prod_comp_name,prod_comp_name_spel,prod_comp_status,")
			.append(" area_code,area_name,ar_shortname,ar_fullname,del_comp_code,del_comp_name,del_comp_name_spel,del_comp_status,hosp_code,")
			.append(" hosp_name,hosp_spel_code,hosp_status,del_range,status,upd_user,upd_datetime)")
			.append(" select :delRelId,:goodsCode,:prodCompCode,:prodCompName,:prodCompSpel,:prodCompStatus,:areaCode,:areaName,:arShortName,")
			.append(" :arFullName,a.comp_code,a.comp_name,a.comp_spel_code,a.status,:hospCode,:hospName,:hospSpelCode,:hospStatus,:delRange,")
			.append(" 1,:userId,SYSDATE() from std_compinfo a where a.comp_code=:compCode");
			Map<String, Object> params = new HashMap<String, Object>();
			params.put("delRelId", uuid);
			params.put("goodsCode", null);
			params.put("prodCompCode", comp.get("comp_code"));
			params.put("prodCompName", comp.get("comp_name"));
			params.put("prodCompSpel", comp.get("comp_spel_code"));
			params.put("prodCompStatus", comp.get("status"));
			params.put("areaCode", area.get("area_id"));
			params.put("areaName", area.get("area_name"));
			params.put("arShortName", area.get("ar_shortname"));
			params.put("arFullName", area.get("ar_fullname"));
			params.put("hospCode", hosp.get("hosp_code"));
			params.put("hospName", hosp.get("hosp_name"));
			params.put("hospSpelCode", hosp.get("hosp_spel_code"));
			params.put("hospStatus", hosp.get("status"));
			params.put("delRange", hosp.get("hosp_classify"));
			params.put("userId", map.get("userId"));
			params.put("compCode", data.getDelCompCode());
			dao.executeSql(sql.toString(), params);
			StringBuffer logsql = new StringBuffer()
			.append("insert into base_delrelationship_log")
			.append(" select UUID(),t.* from base_delrelationship t where t.del_rel_id= :delRelId");
			Map<String, Object> logparams = new HashMap<String, Object>();
			logparams.put("delRelId", uuid);
			dao.executeSql(logsql.toString(), logparams);
		}
	}
	public Pagination getDelByHospLogData(Pagination page){
		StringBuffer sql = new StringBuffer()
		.append("select t.del_comp_code,t.del_comp_name,a.org_code,b.contact_name,b.contact_telephone,t.status,t.upd_datetime ")
		.append(" from base_delrelationship_log t")
		.append(" left join std_hospinfo a on a.hosp_code=t.hosp_code")
		.append(" left join std_hospinfo_detail b on b.hosp_id=a.hosp_id")
		.append("  where t.hosp_code=:hospCode and t.goods_code= :goodsCode");
		Map<String, Object> params = new HashMap<String, Object>();
		Map<String, String> map = page.getConditions();
		params.put("hospCode", map.get("hospCode"));
		params.put("goodsCode", map.get("goodsCode"));
		if (StringUtils.isNotBlank(map.get("compCode"))){
			sql.append(" and t.del_comp_code = :compCode");
			params.put("compCode", map.get("compCode"));
		}
		if (StringUtils.isNotBlank(map.get("compName"))){
			sql.append(" and (t.del_comp_name like :compName or t.del_comp_name_spel like :compNameSpel)");
			params.put("compName", "%" + map.get("compName").trim() + "%");
			params.put("compNameSpel", "%" + map.get("compName").trim().toUpperCase() + "%");
		}
		if (StringUtils.isNotBlank(map.get("startDate"))){
			sql.append(" and DATE_FORMAT(t.upd_datetime,'%Y-%m-%d') >= :startDate");
			params.put("startDate", map.get("startDate"));
		}
		if (StringUtils.isNotBlank(map.get("endDate"))){
			sql.append(" and DATE_FORMAT(t.upd_datetime,'%Y-%m-%d') <= :endDate");
			params.put("endDate", map.get("endDate"));
		}
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}
	
	/**
	 * 
	 *@category 
	 *@author 邱磊
	 *@date 2015年11月23日 下午5:47:16
	 *@param
	 */
	public Pagination getDelProdCompData(Pagination page){
		StringBuffer sql = new StringBuffer()
		.append("select DISTINCT a.comp_code,b.comp_name,b.org_code,c.area_name,d.contact_name,d.contact_telephone ")
		.append(" from bid_projdrugs a")
		.append(" left join std_compinfo b on a.comp_code=b.comp_code")
		.append(" left join dic_area c on c.area_id=b.area_id")
		.append(" left join std_compinfo_detail d on d.comp_id=b.comp_id")
		.append(" where a.status=1");
		Map<String, Object> params = new HashMap<String, Object>();
		Map<String, String> map = page.getConditions();
		if (StringUtils.isNotBlank(map.get("compCode"))){
			sql.append(" and a.comp_code = :compCode");
			params.put("compCode", map.get("compCode"));
		}
		if (StringUtils.isNotBlank(map.get("compName"))){
			sql.append(" and (b.comp_name like :compName or b.comp_spel_code like :compNameSpel)");
			params.put("compName", "%" + map.get("compName").trim() + "%");
			params.put("compNameSpel", "%" + map.get("compName").trim().toUpperCase() + "%");
		}
		return dao.getSqlPageMapList(sql.toString(), params, page);
	}
	@SuppressWarnings("unchecked")
	public Pagination getBaseDelCentreWithHospList(Pagination page){
		StringBuffer sql = new StringBuffer()
		.append("select a.del_rel_id,a.hosp_code,a.hosp_name,")
		.append("a.prod_comp_code,a.prod_comp_name,a.area_name,a.del_comp_name,")
		.append("a.del_comp_code");
		StringBuffer sqlCount = new StringBuffer("select count(*) ");
		StringBuffer sqlEnd = new StringBuffer().append(" from base_delrelationship a ")
		.append(" left join dic_area e on e.area_id=a.area_code")
		.append("  where a.status=1");
		sqlCount.append(sqlEnd);
		sql.append(sqlEnd);
		Map<String, Object> params = new HashMap<String, Object>();
		Map<String, String> map = page.getConditions();
		if (null != map && map.size() > 0){
			if (StringUtils.isNotBlank(map.get("compName"))){
				sql.append(" and (a.prod_comp_name like :compName or a.prod_comp_name_spel like :compNameSpel)");
				sqlCount.append(" and (a.prod_comp_name like :compName or a.prod_comp_name_spel like :compNameSpel)");
				params.put("compName", "%"+map.get("compName").trim()+"%");
				params.put("compNameSpel", "%"+map.get("compName").trim().toUpperCase()+"%");
			}
			if (StringUtils.isNotBlank(map.get("areaCode"))){
				sql.append(" and a.area_code = :areaCode");
				sqlCount.append(" and a.area_code = :areaCode");
				params.put("areaCode", map.get("areaCode").trim());
			}
			if (StringUtils.isNotBlank(map.get("fatherCode"))){
				sql.append(" and e.father_id = :fatherCode");
				sqlCount.append(" and e.father_id = :fatherCode");
				params.put("fatherCode", map.get("fatherCode").trim());
			}
			if (StringUtils.isNotBlank(map.get("delCompName"))){
				sql.append(" and (a.del_comp_name like :delName or a.del_comp_name_spel like :delNameSpel)");
				sqlCount.append(" and (a.del_comp_name like :delName or a.del_comp_name_spel like :delNameSpel)");
				params.put("delName", "%"+map.get("delCompName").trim()+"%");
				params.put("delNameSpel", "%"+map.get("delCompName").trim().toUpperCase()+"%");
			}
			if (StringUtils.isNotBlank(map.get("hospName"))){
				sql.append(" and (a.hosp_name like :hospName or a.hosp_spel_code like :hospNameSpel)");
				sqlCount.append(" and (a.hosp_name like :hospName or a.hosp_spel_code like :hospNameSpel)");
				params.put("hospName", "%"+map.get("hospName").trim()+"%");
				params.put("hospNameSpel", "%"+map.get("hospName").trim().toUpperCase()+"%");
			}
		}
		
        Query query = getSession().createSQLQuery(sql.toString());
		if (params != null) {
			Iterator<?> list = params.entrySet().iterator();
			while (list.hasNext()) {
				Map.Entry<String, ?> entry = (Map.Entry<String, ?>) list.next();
				query.setParameter((String) entry.getKey(), entry.getValue());
			}
		}
		if (page.getMaxResults() > 0) {			
			Query countQuery = getSession().createSQLQuery(sqlCount.toString());
			if (params != null) {
				Iterator<?> list = params.entrySet().iterator();
				while (list.hasNext()) {
					Map.Entry<String, ?> entry = (Map.Entry<String, ?>) list.next();
					countQuery.setParameter((String) entry.getKey(), entry.getValue());
				}
			}
			BigInteger records = (BigInteger) countQuery.uniqueResult();
			page.setRecords(records.longValue());
		}
		query.setFirstResult(page.getFirstResult());
		query.setMaxResults(page.getMaxResults());
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		List<?> list = query.list();
		page.setRows(list);
		return page;
	}
	
	
	/**
	 * @category 配送企业获取待确认配送的商品数据
	 * @author 韩守松
	 * @date   2015年12月8日
	 * @param  @return
	 */
	@SuppressWarnings("unchecked")
	public Pagination getWaitDelOKGoods(Pagination page){
		StringBuffer sql = new StringBuffer()
		.append("SELECT a.del_rel_id,a.goods_code,b.drug_code,b.qualityspec_name drug_name,b.form_name,")
		.append("b.spec_name,b.factor,b.unit_name unit,b.material_name,b.prod_compname comp_name,a.area_name,b.pro_price_limit,b.drug_code drug_id,b.prod_compcode prod_comp_id, ")
		.append("a.status  ");
		StringBuffer sqlCount = new StringBuffer().append("select count(*) ");
		
		StringBuffer sqlEnd = new StringBuffer()
		.append(" from base_delrelationship a left join base_goods_item b on a.goods_code=b.goods_code")
		.append(" where a.status=:status and b.status=1  and b.proj_status=1")
		.append(" and DATE_FORMAT(b.proj_starttime,'%Y-%m-%d') <= DATE_FORMAT(SYSDATE(),'%Y-%m-%d') and DATE_FORMAT(b.proj_endtime,'%Y-%m-%d') >= DATE_FORMAT(SYSDATE(),'%Y-%m-%d')")
		.append(" and a.del_comp_code=:compCode");
		
		sql.append(sqlEnd);
		sqlCount.append(sqlEnd);
		
		Map<String, String> map = page.getConditions();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("status", map.get("status").trim());
		params.put("compCode", map.get("compCode").trim());
		if (StringUtils.isNotBlank(map.get("goodsCode"))) {
			sql.append(" and a.goods_code =:goodsCode");
			sqlCount.append(" and a.goods_code =:goodsCode");
			params.put("goodsCode", map.get("goodsCode").trim());
		}
		if (StringUtils.isNotBlank(map.get("drugCode"))) {
			sql.append(" and b.drug_code =:drugCode");
			sqlCount.append(" and b.drug_code =:drugCode");
			params.put("drugCode", map.get("drugCode").trim());
		}
		if (StringUtils.isNotBlank(map.get("drugName"))) {
			if (PinYin.isPinYin(map.get("drugName").trim())) {
				sql.append(" and b.qualityspec_name_spel like :drugName");
				sqlCount.append(" and b.qualityspec_name_spel like :drugName");
				params.put("drugName", "%" + map.get("drugName").trim().toUpperCase() + "%");
			} else {
				sql.append(" and b.qualityspec_name like :drugName");
				sqlCount.append(" and b.qualityspec_name like :drugName");
				params.put("drugName", "%" + map.get("drugName").trim() + "%");
			}
		
		}
		if (StringUtils.isNotBlank(map.get("compName"))) {
			sql.append(" and (b.prod_compname like :compName or b.prod_compname_spel like :compSpelCode)");
			sqlCount.append(" and (b.prod_compname like :compName or b.prod_compname_spel like :compSpelCode)");
			params.put("compName", "%" + map.get("compName").trim() + "%");
			params.put("compSpelCode",  "%" + map.get("compName").trim().toUpperCase() + "%");
		}
		if (StringUtils.isNotBlank(map.get("areaCode"))) {
			sql.append(" and a.area_code=:areaCode");
			sqlCount.append(" and a.area_code=:areaCode");
			params.put("areaCode", map.get("areaCode").trim());
		}
		if (StringUtils.isNotBlank(map.get("fatherCode"))) {
			sql.append(" and a.area_code like :fatherCode");
			sqlCount.append(" and a.area_code like :fatherCode");
			params.put("fatherCode", AreaUtil.getAreaPrefix(map.get("fatherCode").trim())+"%");
		}
		Query query = sessionFactory.getCurrentSession().createSQLQuery(sql.toString());
		if (params != null) {
			Iterator<?> list = params.entrySet().iterator();
			while (list.hasNext()) {
				Map.Entry<String, ?> entry = (Map.Entry<String, ?>) list.next();
				query.setParameter((String) entry.getKey(), entry.getValue());
			}
		}
		if (page.getMaxResults() > 0) {			
			Query countQuery = sessionFactory.getCurrentSession().createSQLQuery(sqlCount.toString());
			if (params != null) {
				Iterator<?> list = params.entrySet().iterator();
				while (list.hasNext()) {
					Map.Entry<String, ?> entry = (Map.Entry<String, ?>) list.next();
					countQuery.setParameter((String) entry.getKey(), entry.getValue());
				}
			}
			BigInteger records = (BigInteger) countQuery.uniqueResult();
			page.setRecords(records.longValue());
		}
		query.setFirstResult(page.getFirstResult());
		query.setMaxResults(page.getMaxResults());
		
		List<?> list = query.list();
		page.setRows(list);
		
		return page;
	}
}
